/*
	CREATE 
 	This Prodedure Loads all the Discrepancy Lots from Notification Alert Table.
	SELECT * FROM NOTIFICATION_ALERT_TABLE WHERE NOTIFICATION_NAME = 'Discrepancy Lot Notification' and lot_number = 'MMN6Y'
	EXEC sp_load_all_lot_qty_discrepancy_reports 'Fab Option','0','cmi','0','','xlreport','0'
*/
ALTER  PROC sp_load_all_lot_qty_discrepancy_reports 

@PROCESS_TYPE_NAME	VARCHAR(50),
@STEP_ID		VARCHAR(50),
@VENDOR			VARCHAR(50),
@AS_OF_DATE 		VARCHAR(50),
@SEARCH_PART_NO		VARCHAR(50),
@REPORT_TYPE		VARCHAR(50),  
@DEFAULT_PROMPT 	VARCHAR(50) 

AS
BEGIN
	SET NOCOUNT ON
	CREATE TABLE #RESULTS_TABLE ( 	LOT_NUMBER 		VARCHAR(50),
					FAB_LOT_NUMBER 		VARCHAR(50),
	  				PART_NUMBER 		VARCHAR(50),
					PROCESS_TYPE 		VARCHAR(50),
	  				VENDOR_NAME 		VARCHAR(50),
	  				PREV_TOOL_QTY 		INT,
	  				PREV_DATE_IN 		DATETIME,
	  				CURRENT_TOOL_QTY	INT,
	  				CURRENT_DATEIN 		DATETIME,
	  				QTY_DIFF 		INT, 
	  				TYPE 			VARCHAR(100),
	 				PREV_WAFER_QTY 		INT,
					CURRENT_WAFER_QTY 	INT,
					WAFER_QTYDIFF 		INT,
					PS_TYPE_ID 		INT )

	DECLARE @PART_NUMBER	VARCHAR(50)
	DECLARE @LOT_NUMBER	VARCHAR(50)
	DECLARE @FAB_LOT_NUMBER	VARCHAR(50)
	DECLARE @VENDOR_NAME	VARCHAR(50)
	DECLARE @PROCESS_TYPE	VARCHAR(50)

	DECLARE @SQL_STRING	VARCHAR(4000)
	DECLARE @OBSOLETE_STRING VARCHAR(4000)

	--DECLARE FLOWCURSOR CURSOR

	SET @SQL_STRING = N' DECLARE FLOWCURSOR CURSOR FOR
	SELECT DISTINCT PART_NUMBER,LOT_NUMBER,FAB_LOT_NUMBER,VENDOR_NAME,PROCESS_TYPE--,TIMESTAMP
	FROM NOTIFICATION_ALERT_TABLE 
	WHERE NOTIFICATION_NAME = ''Discrepancy Lot Notification'' 
	AND STATUS = ''OPEN'' '

	IF ( @PROCESS_TYPE_NAME <> '0' )
	BEGIN
		SET @SQL_STRING = @SQL_STRING + ' AND  PROCESS_TYPE = '''+ @PROCESS_TYPE_NAME +''''
	END
	IF (  @VENDOR <> '0' )
	BEGIN
		SET @SQL_STRING = @SQL_STRING + ' AND  VENDOR_NAME = '''+ @VENDOR +''''
	END
	IF ( @SEARCH_PART_NO <> '' and LEN(LTRIM(RTRIM(@SEARCH_PART_NO))) > 0 )
	BEGIN
		SET @SQL_STRING = @SQL_STRING + ' AND  PART_NUMBER like  ''' + '%' + @search_Part_no + '%' + ''''
	END

	EXEC (@SQL_STRING)

	OPEN FLOWCURSOR 
	FETCH NEXT FROM FLOWCURSOR INTO @PART_NUMBER, @LOT_NUMBER, @FAB_LOT_NUMBER, @VENDOR_NAME, @PROCESS_TYPE
	WHILE @@FETCH_STATUS = 0
	BEGIN
		INSERT INTO #RESULTS_TABLE (PART_NUMBER,LOT_NUMBER,FAB_LOT_NUMBER,VENDOR_NAME,PROCESS_TYPE,CURRENT_TOOL_QTY,CURRENT_WAFER_QTY,CURRENT_DATEIN,PS_TYPE_ID,TYPE)
				/*VALUES*/  SELECT TOP 1 PART_NUMBER,LOT_NUMBER,FAB_LOT_NUMBER,VENDOR_NAME,PROCESS_TYPE,DIE_QTY,WAFER_QTY,TIMESTAMP,
						 (CASE
							WHEN PROCESS_TYPE = 'FAB' THEN 1
							WHEN PROCESS_TYPE = 'FAB OPTION' THEN 2
							WHEN PROCESS_TYPE = 'WAFER store' THEN 3
							WHEN PROCESS_TYPE = 'Die Bank' THEN 4
							WHEN PROCESS_TYPE = 'ASSEMBLY' THEN  5
							WHEN PROCESS_TYPE = 'FPO' THEN 6
							WHEN PROCESS_TYPE = 'FT'  THEN  7
							WHEN PROCESS_TYPE = 'class store' THEN 8
							WHEN PROCESS_TYPE = 'Finished Goods' THEN 9
						END ),'PART NUMBER NOT AVAILABLE'
					 FROM 
						NOTIFICATION_ALERT_TABLE
					 WHERE 
						NOTIFICATION_NAME = 'Discrepancy Lot Notification'
						AND PART_NUMBER = @PART_NUMBER 
						AND LOT_NUMBER = @LOT_NUMBER 
						AND FAB_LOT_NUMBER = @FAB_LOT_NUMBER
						AND VENDOR_NAME = @VENDOR_NAME
						AND PROCESS_TYPE = @PROCESS_TYPE
						AND STATUS = 'OPEN'
					ORDER BY TIMESTAMP DESC

			SET @PART_NUMBER	= NULL
			SET @LOT_NUMBER 	= NULL
			SET @FAB_LOT_NUMBER	= NULL
			SET @VENDOR_NAME	= NULL
			SET @PROCESS_TYPE	= NULL
		 
	FETCH NEXT FROM FLOWCURSOR INTO @PART_NUMBER, @LOT_NUMBER, @FAB_LOT_NUMBER, @VENDOR_NAME, @PROCESS_TYPE
	END
	CLOSE FLOWCURSOR
	DEALLOCATE FLOWCURSOR	
	
	IF @REPORT_TYPE = 'XLREPORT'
	BEGIN
		SELECT 
			TYPE [Description] ,PART_NUMBER [Part Number] ,LOT_NUMBER [Lot Number] ,FAB_LOT_NUMBER [Fab Lot Number] ,
			VENDOR_NAME [Vendor] ,PROCESS_TYPE [Process] ,PREV_DATE_IN [Prev Tool Date In] ,CURRENT_DATEIN [Current Tool Date In] ,
			PREV_TOOL_QTY [Prev Tool Qty] ,CURRENT_TOOL_QTY [Current Tool Qty] ,QTY_DIFF [Qty Diff] ,
			PREV_WAFER_QTY [Prev Wafer Qty] ,CURRENT_WAFER_QTY [Current Wafer Qty],WAFER_QTYDIFF [Wafer Qty Diff]
		FROM 
			#RESULTS_TABLE  
		WHERE
			( ISNULL( CURRENT_WAFER_QTY, 0 ) > 0 OR isnull(CURRENT_TOOL_QTY,0) > 0 )
		ORDER BY 
			PS_TYPE_ID,VENDOR_NAME,FAB_LOT_NUMBER,LOT_NUMBER
	END
	ELSE
	BEGIN
		SELECT 
			LOT_NUMBER,FAB_LOT_NUMBER ,PART_NUMBER ,PROCESS_TYPE ,VENDOR_NAME ,PREV_TOOL_QTY ,PREV_DATE_IN ,
			CURRENT_TOOL_QTY,CURRENT_DATEIN ,QTY_DIFF ,TYPE ,PREV_WAFER_QTY ,CURRENT_WAFER_QTY ,WAFER_QTYDIFF ,PS_TYPE_ID 
		FROM 
			#RESULTS_TABLE  
		WHERE
			( ISNULL( CURRENT_WAFER_QTY, 0 ) > 0 OR isnull(CURRENT_TOOL_QTY,0) > 0 )
		ORDER BY 
			PS_TYPE_ID,VENDOR_NAME,FAB_LOT_NUMBER,LOT_NUMBER
	END

SET NOCOUNT OFF
END